using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Reflection;
using System.IO;
using System.Collections.Generic;
using RandomGenerationFramework;
using System.Data.Common;
using System.Diagnostics;


namespace DBRandomGenerator
{
    public  class DCM
    {
        public static string ConnectionString = "";
        public static string SqlStatement;
        

        public DataTable GetTableList()
        {
            SqlConnection RDGenConn = new SqlConnection(ConnectionString);
            StreamReader sr =new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(Assembly.GetExecutingAssembly().GetName().Name+".TableList.txt"));
            SqlStatement = sr.ReadToEnd();
            SqlDataAdapter RDGenAdapter = new SqlDataAdapter(SqlStatement, RDGenConn);
            DataTable TableList = new DataTable();
            RDGenAdapter.Fill(TableList);
            return TableList;
        }

        public IDataReader GetForeignKeyValue(Column TableColumn, string TableName)
        {
            string ColumnValue = "";
            SqlStatement = " SELECT  TOP 1 ";
            SqlStatement = SqlStatement + TableColumn.ParentKeyColumn.ToString();
            SqlStatement = SqlStatement + " FROM ";
            SqlStatement = SqlStatement + TableColumn.ParentKeyTable.ToString();
            SqlStatement = SqlStatement + " ORDER BY NewId() ";

            SqlConnection RDGenConn = new SqlConnection(ConnectionString);
            SqlCommand RDGenCommand = new SqlCommand(SqlStatement, RDGenConn);
            RDGenConn.Open();

            SqlDataReader RDGenDataReader = RDGenCommand.ExecuteReader();

            // modified this piece to include IF statement with majority of the changes in the ELSE portion
            //if (RDGenDataReader.HasRows)
            //{
            //    foreach (DbDataRecord RDGenRecord in RDGenDataReader)
            //    {
            //        ColumnValue = RDGenRecord[0].ToString();
            //    }
            //}
            //else
            //{
            //    // insert a single row into the table in question, and then call this method again to retrieve the foreignkey value
            //    GetColumnList(TableColumn.ParentKeyTable.ToString());

            //    RDGenTable RDGenSelectedTable = new RDGenTable(TableColumn.ParentKeyTable.ToString());
            //    StreamWriter sr = new StreamWriter(TableColumn.ParentKeyTable.ToString() + ".sql");
            //    for (int i = 0; i <= 1; i++)
            //    {
            //        sr.WriteLine(RDGenSelectedTable.GetInsertStatement());
            //        sr.WriteLine("");

            //    }
            //    sr.Close();
            //}
            //RDGenDataReader.Close();
            //RDGenConn.Close();
            return RDGenDataReader;
        }

        //public string GetForeignKeyValue(Column TableColumn,string TableName)
        //{
        //    string ColumnValue = "";
        //    SqlStatement = " SELECT  TOP 1 ";
        //    SqlStatement = SqlStatement +  TableColumn.ParentKeyColumn.ToString();
        //    SqlStatement = SqlStatement + " FROM ";
        //    SqlStatement = SqlStatement + TableColumn.ParentKeyTable.ToString();
        //    SqlStatement = SqlStatement + " ORDER BY NewId() ";

        //    SqlConnection RDGenConn = new SqlConnection(ConnectionString);
        //    SqlCommand RDGenCommand = new SqlCommand(SqlStatement, RDGenConn);
        //    RDGenConn.Open();

        //    SqlDataReader RDGenDataReader = RDGenCommand.ExecuteReader();

        //    // modified this piece to include IF statement with majority of the changes in the ELSE portion
        //    if (RDGenDataReader.HasRows)
        //    {
        //        foreach (DbDataRecord RDGenRecord in RDGenDataReader)
        //        {
        //            ColumnValue = RDGenRecord[0].ToString();
        //        }
        //    }
        //    else
        //    {
        //          // insert a single row into the table in question, and then call this method again to retrieve the foreignkey value
        //        GetColumnList(TableColumn.ParentKeyTable.ToString());

        //        RDGenTable RDGenSelectedTable = new RDGenTable(TableColumn.ParentKeyTable.ToString());
        //        StreamWriter sr = new StreamWriter(TableColumn.ParentKeyTable.ToString() + ".sql");
        //        for (int i = 0; i <= 1; i++)
        //        {
        //            sr.WriteLine(RDGenSelectedTable.GetInsertStatement());
        //            sr.WriteLine("");
            
        //        }
        //        sr.Close();
        //    }
        //    RDGenDataReader.Close();
        //    RDGenConn.Close();
        //    return ColumnValue;
        //}

        public static List<Column> GetColumnList(string TableName)
        {
            /*
             * First get the list of foreign keys, Parent keys and Parent Key Tables.
            
            */
            List<ForeignKey> RDGenForeignKeys = new List<ForeignKey>();
            SqlConnection RDGenConn = new SqlConnection(ConnectionString);
            SqlCommand RDGenCommand = new SqlCommand("sp_fkeys", RDGenConn);
            RDGenCommand.Parameters.AddWithValue("@fktable_name", TableName);
            RDGenConn.Open();

            RDGenCommand.CommandType = CommandType.StoredProcedure;
            SqlDataReader RDGenForeignKeyReader = RDGenCommand.ExecuteReader();
            

            foreach(DbDataRecord ForeignKeyRecord in RDGenForeignKeyReader)
            {
                ForeignKey RDGenForeignKey = new ForeignKey();
                RDGenForeignKey.PKColumnName = (string)ForeignKeyRecord["PKCOLUMN_NAME"];
                RDGenForeignKey.PKTableName = (string)ForeignKeyRecord["PKTABLE_NAME"];
                RDGenForeignKey.FKColumnName=(string)ForeignKeyRecord["FKCOLUMN_NAME"];
                RDGenForeignKeys.Add(RDGenForeignKey);
            }
            /* RDGenForeignKeyReader closed -- nov 3rd 2009*/
            RDGenForeignKeyReader.Close();

           /* Get all the columns and check if the column is a foreign key*/
            List<Column> RDGenColumns = new List<Column>();
            StreamReader sr = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(Assembly.GetExecutingAssembly().GetName().Name+".ColumnList.txt"));
            SqlStatement =sr.ReadToEnd();
            sr.Close();
            SqlStatement =SqlStatement.Replace("#TABLE_NAME#",TableName);
            RDGenCommand = new SqlCommand(SqlStatement, RDGenConn);
            
            SqlDataReader RDGenReader = RDGenCommand.ExecuteReader(CommandBehavior.CloseConnection);
            foreach(System.Data.Common.DbDataRecord  ColumnRow in RDGenReader)
            {
                Column TableColumn = new Column();   
                TableColumn.Name = (string)ColumnRow[0];
                TableColumn.DataType = (string)ColumnRow[1];

                TableColumn.Length = (int)ColumnRow[2] <= 0 ? GetTypeMax((string)ColumnRow[1]) : (int)ColumnRow[2] ;
                TableColumn.Nullable = ((string)ColumnRow[3] == "Y" ? true : false);
                TableColumn.ForeignKey = ((string)ColumnRow[4] == "Y" ? true : false);
                //added Nov 8 to help identify GUID fields better
                TableColumn.IsGuid = (TableColumn.DataType.ToLower() == "uniqueidentifier");
                /* if the column is a foreign key find the parent key and table */
                if (TableColumn.ForeignKey)
                {
                    foreach (ForeignKey RDGenTableForeignKey in RDGenForeignKeys)
                    {
                        if (RDGenTableForeignKey.FKColumnName == TableColumn.Name)
                        {
                            TableColumn.ParentKeyColumn = RDGenTableForeignKey.PKColumnName;
                            TableColumn.ParentKeyTable = RDGenTableForeignKey.PKTableName;
                            
                        }
                    }
                }

                TableColumn.OrdinalPosition = (int)ColumnRow[5];
                TableColumn.IsComputed = ((int)ColumnRow[6] == 1? true : false);
                TableColumn.IsIdentity = ((int)ColumnRow[7] == 1 ? true : false);
                // added line below Nov 9th to help to make the IncludeColumn property available from start
                TableColumn.IncludeColumn = IncludeColumn(TableColumn);

                RDGenColumns.Add(TableColumn);

            }


            RDGenReader.Close();
            
            return RDGenColumns;
        }

        public static bool IncludeColumn(Column TableColumn)
        {
            //added November 9th to help remove some of the processing from particular objects
            bool ConditionSet1 = (!(TableColumn.IsComputed || TableColumn.IsIdentity));
            ConditionSet1 = ConditionSet1 && (!(TableColumn.DataType.ToLower() == "sysname" || TableColumn.DataType.ToLower() == "timestamp" || TableColumn.DataType.ToLower() == "image"));
            return (ConditionSet1);
        }

        static int GetTypeMax(String columntype)
        {
            int temp = 0;
            switch (columntype.ToLower())
            {
                case "char":
                    temp = RandomGeneratorHelper.GetRandomInteger(1, 5);
                    break;
                case "varchar":
                    temp = RandomGeneratorHelper.GetRandomInteger(50, 1500);
                    break;
                case "nchar":
                    temp = RandomGeneratorHelper.GetRandomInteger(1, 30);
                    break;
                case "nvarchar":
                    temp = RandomGeneratorHelper.GetRandomInteger(1, 1000);
                    break;
                case "text":
                    temp = RandomGeneratorHelper.GetRandomInteger(200, 1500);
                    break;
                case "ntext":
                    temp = RandomGeneratorHelper.GetRandomInteger(350, 800);
                    break;

            }
            return temp;

        }
    }
}
